﻿using System.Collections.Generic;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using Softelligent.Zintra.Synchronizations.ProductsAccessConsole.Entities;
using Softelligent.Zintra.Synchronizations.ProductsAccessConsole.Properties;

namespace Softelligent.Zintra.Synchronizations.ProductsAccessConsole
{
    public class ProductRepository
    {
        private readonly string connectionString;

        public ProductRepository()
        {
            connectionString = Settings.Default.ClientConnectionString;
        }

        #region Categories

        private Category InsertCategory(string name)
        {
            using (var connection = new OleDbConnection(connectionString))
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "INSERT INTO Categories (Name) VALUES ( ? )";
                command.Parameters.AddWithValue("Name", name);

                var idCommand = connection.CreateCommand();
                idCommand.CommandText = "SELECT @@IDENTITY";

                connection.Open();

                command.ExecuteNonQuery();

                return new Category
                           {
                               Id = (int)idCommand.ExecuteScalar(),
                               Name = name
                           };
            }
        }

        private IList<ProductCategory> FetchCategoriesByProductId(int productId)
        {
            var categories = new List<ProductCategory>();
            using (var connection = new OleDbConnection(connectionString))
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SELECT c.ID, c.Name FROM ProductCategory AS pc INNER JOIN Categories AS c ON pc.CategoryID = c.ID WHERE pc.ProductID = ?";
                command.Parameters.AddWithValue("ProductID", productId);

                connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            categories.Add(new ProductCategory
                            {
                                Id = reader.GetInt32(0),
                                Name = reader.GetString(1)
                            });
                        }
                    }
                }
            }

            return categories;
        }

        private IList<Category> FetchCategoriesByNames(IEnumerable<string> names)
        {
            var categories = new List<Category>();
            if (!names.Any())
            {
                return categories;
            }

            var queryBuilder = new StringBuilder();
            queryBuilder.Append("SELECT ID, Name FROM Categories WHERE");
            var namesCount = names.Count();
            for (var i = 1; i < namesCount; i++)
            {
                queryBuilder.Append(" Name = ? OR");
            }
            queryBuilder.Append(" Name = ?");


            using (var connection = new OleDbConnection(connectionString))
            using (var command = connection.CreateCommand())
            {
                command.CommandText = queryBuilder.ToString();
                for (var i = 0; i < namesCount; i++)
                {
                    command.Parameters.AddWithValue("Name" + i, names.ElementAt(i));
                }

                connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            categories.Add(new Category
                            {
                                Id = reader.GetInt32(0),
                                Name = reader.GetString(1)
                            });
                        }
                    }
                }
            }

            return categories;
        }

        #endregion

        private Product AssembleProduct(OleDbDataReader reader)
        {
            return new Product
            {
                Identifier = reader.GetInt32(0),
                IsArchived = reader.GetBoolean(1),
                IsFeatured = reader.GetBoolean(2),
                IsDeleted = reader.GetBoolean(3),
                IsHidden = reader.GetBoolean(4),
                Summary = reader.GetString(5),
                Description = reader.GetString(6),
                Name = reader.GetString(7),
                Reference = reader.GetString(8),
                SEOName = reader.GetString(9),
                Manufacturer = reader.GetString(10),
                CreatedDate = reader.GetDateTime(11)
            };
        }

        public Product Fetch(string id)
        {
            int entityId;
            if (!int.TryParse(id, out entityId))
            {
                return null;
            }

            Product product = null;
            using (var connection = new OleDbConnection(connectionString))
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM Products WHERE ProductID = ?";
                command.Parameters.AddWithValue("ProductID", entityId);

                connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    if (reader != null && reader.Read())
                    {
                        product = AssembleProduct(reader);
                    }
                }
            }

            product.Categories = FetchCategoriesByProductId(product.Identifier);

            return product;
        }

        public Batch FetchAll(int batchSize, int batchNumber)
        {
            var products = new List<Product>();
            using (var connection = new OleDbConnection(connectionString))
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM Products";

                connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            products.Add(AssembleProduct(reader));
                        }
                    }
                }
            }

            // TODO fetch all categories by productIds and assemble them
            foreach (var product in products)
            {
                product.Categories = FetchCategoriesByProductId(product.Identifier);
            }

            return new Batch { Items = products, Count = 1, Number = 1 };
        }

        public IEnumerable<string> FetchNotExisting(IEnumerable<string> ids)
        {
            if (!ids.Any())
            {
                return new List<string>();
            }

            var existing = new List<string>();
            var queryBuilder = new StringBuilder();
            queryBuilder.Append("SELECT ProductID FROM Products WHERE");
            var idsCount = ids.Count();
            for (var i = 1; i < idsCount; i++)
            {
                queryBuilder.Append(" ProductID = ? OR");
            }
            queryBuilder.Append(" ProductID = ?");

            using (var connection = new OleDbConnection(connectionString))
            using (var command = connection.CreateCommand())
            {
                command.CommandText = queryBuilder.ToString();
                for (var i = 0; i < idsCount; i++)
                {
                    command.Parameters.AddWithValue("ProductID" + i, ids.ElementAt(i));
                }

                connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    if (reader != null)
                    {
                        while (reader.Read())
                        {
                            existing.Add(reader.GetInt32(0).ToString());
                        }
                    }
                }
            }

            return ids.Where(id => !existing.Contains(id));
        }

        private bool HasProduct(int id)
        {
            if (id < 0)
            {
                return false;
            }

            var hasProduct = false;
            using (var connection = new OleDbConnection(connectionString))
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SELECT ProductID FROM Products WHERE ProductID = ?";
                command.Parameters.AddWithValue("ProductID", id);

                connection.Open();

                using (var reader = command.ExecuteReader())
                {
                    hasProduct = reader != null && reader.Read();
                }
            }

            return hasProduct;
        }

        public Product Save(Product product)
        {
            if (product.Identifier < 0)
            {
                return null;
            }

            var categoryNames = product.Categories.Select(c => c.Name);
            var existingCategories = FetchCategoriesByNames(categoryNames);
            var nonExistingCategories = categoryNames.Where(cn => !existingCategories.Any(ec => ec.Name == cn));
            foreach (var nonExistingCategory in nonExistingCategories)
            {
                existingCategories.Add(InsertCategory(nonExistingCategory));
            }

            var isFound = HasProduct(product.Identifier);
            if (isFound)
            {
                var existingProductCategories = FetchCategoriesByProductId(product.Identifier);
                var nonExistingProductCategories = existingCategories.Where(c => !existingProductCategories.Any(ec => ec.Name == c.Name));
                using (var connection = new OleDbConnection(connectionString))
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "UPDATE Products SET [Product Name] = ?, [Description] = ?, [Summary] = ?, [Product Ref] = ?, [IsHidden] = ?, [Featured] = ?, [IsDeleted] = ?, [Archived] = ?, [SEO Name] = ?, [Manufacturer] = ? WHERE ProductID = ?";
                    command.Parameters.AddWithValue("[Product Name]", product.Name);
                    command.Parameters.AddWithValue("[Description]", product.Description);
                    command.Parameters.AddWithValue("[Summary]", product.Summary);
                    command.Parameters.AddWithValue("[Product Ref]", product.Reference);
                    command.Parameters.AddWithValue("[IsHidden]", product.IsHidden);
                    command.Parameters.AddWithValue("[Featured]", product.IsFeatured);
                    command.Parameters.AddWithValue("[IsDeleted]", product.IsDeleted);
                    command.Parameters.AddWithValue("[Archived]", product.IsArchived);
                    command.Parameters.AddWithValue("[SEO Name]", product.SEOName);
                    command.Parameters.AddWithValue("[Manufacturer]", product.Manufacturer);
                    command.Parameters.AddWithValue("ProductID", product.Identifier);

                    var categoryCommands = new List<OleDbCommand>();
                    foreach (var nonExistingProductCategory in nonExistingProductCategories)
                    {
                        var categoryCommand = connection.CreateCommand();
                        categoryCommand.CommandText = "INSERT INTO ProductCategory (ProductID, CategoryID) VALUES ( ?, ? )";
                        categoryCommand.Parameters.AddWithValue("ProductID", product.Identifier);
                        categoryCommand.Parameters.AddWithValue("CategoryID", nonExistingProductCategory.Id);
                        categoryCommands.Add(categoryCommand);
                    }
                    try
                    {
                        connection.Open();

                        command.ExecuteNonQuery();

                        foreach (var categoryCommand in categoryCommands)
                        {
                            categoryCommand.ExecuteNonQuery();
                        }
                    }
                    finally
                    {
                        foreach (var categoryCommand in categoryCommands)
                        {
                            categoryCommand.Dispose();
                        }
                    }
                }
            }
            else
            {
                using (var connection = new OleDbConnection(connectionString))
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "INSERT INTO Products ([Product Name], [Description], [Product Ref], [Summary], [SEO Name], [IsHidden], [Featured], [IsDeleted], [Archived], [Manufacturer]) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
                    command.Parameters.AddWithValue("[Product Name]", product.Name);
                    command.Parameters.AddWithValue("[Description]", product.Description);
                    command.Parameters.AddWithValue("[Product Ref]", product.Reference);
                    command.Parameters.AddWithValue("[Summary]", product.Summary);
                    command.Parameters.AddWithValue("[SEO Name]", product.SEOName);
                    command.Parameters.AddWithValue("[IsHidden]", product.IsHidden);
                    command.Parameters.AddWithValue("[Featured]", product.IsFeatured);
                    command.Parameters.AddWithValue("[IsDeleted]", product.IsDeleted);
                    command.Parameters.AddWithValue("[Archived]", product.IsArchived);
                    command.Parameters.AddWithValue("[Manufacturer]", product.Manufacturer);

                    var idCommand = connection.CreateCommand();
                    idCommand.CommandText = "SELECT @@IDENTITY";

                    connection.Open();

                    command.ExecuteNonQuery();

                    product.Id = idCommand.ExecuteScalar().ToString();
                }

                using (var connection = new OleDbConnection(connectionString))
                {
                    var categoryCommands = new List<OleDbCommand>();
                    foreach (var nonExistingProductCategory in existingCategories)
                    {
                        var categoryCommand = connection.CreateCommand();
                        categoryCommand.CommandText = "INSERT INTO ProductCategory (ProductID, CategoryID) VALUES ( ?, ? )";
                        categoryCommand.Parameters.AddWithValue("ProductID", product.Identifier);
                        categoryCommand.Parameters.AddWithValue("CategoryID", nonExistingProductCategory.Id);
                        categoryCommands.Add(categoryCommand);
                    }
                    try
                    {
                        connection.Open();

                        foreach (var categoryCommand in categoryCommands)
                        {
                            categoryCommand.ExecuteNonQuery();
                        }
                    }
                    finally
                    {
                        foreach (var categoryCommand in categoryCommands)
                        {
                            categoryCommand.Dispose();
                        }
                    }
                }
            }

            return product;
        }

        public void Delete(string id)
        {
            int entityId;
            if (!int.TryParse(id, out entityId))
            {
                return;
            }

            using (var connection = new OleDbConnection(connectionString))
            using (var categoriesCommand = connection.CreateCommand())
            using (var command = connection.CreateCommand())
            {
                categoriesCommand.CommandText = "DELETE FROM ProductCategory WHERE ProductID = ?";
                categoriesCommand.Parameters.AddWithValue("ProductID", entityId);

                command.CommandText = "DELETE FROM Products WHERE ProductID = ?";
                command.Parameters.AddWithValue("ProductID", entityId);

                connection.Open();

                categoriesCommand.ExecuteNonQuery();

                command.ExecuteNonQuery();
            }
        }
    }
}
